﻿CREATE PROCEDURE [dbo].[spCreateOrderTransactions]
AS

declare @OwnerID int
declare @RetryCardDelay int
declare @RetryCardCount int
declare cOwner cursor local for select OwnerID, coalesce(RetryCardDelay, 0), coalesce(RetryCardCount, 0) 
	from tblOwner
open cOwner
fetch next from cOwner into @OwnerID, @RetryCardDelay, @RetryCardCount
while @@FETCH_STATUS = 0
begin
	-- Get the list of orders that need transactions created
	declare @TransactionOrders table ( OrderID int )
	insert into @TransactionOrders ( OrderID )
	select OrderID
	from tblOrderHeader T1
	where T1.OrderStatusCode = 'P'
		-- Make sure there isn't already a 'Bill' transaction for this order
		and not exists( select 1 from tblTransaction 
			where OrderID = T1.OrderID and TransactionTypeCode='B' and TransactionStatusCode not in ( 'F', 'X' ) )
		-- Make sure we haven't tried to bill within the retry time limit
		and not exists( select 1 from tblTransaction 
			where OrderID = T1.OrderID and TransactionTypeCode='B' and TransactionStatusCode = 'F'
				and DATEADD( day, @RetryCardDelay, ResponseDate ) > GETDATE()
				and @RetryCardDelay > 0 )
		-- Make sure we haven't exceeded the retry count
		and ( select COUNT(*) from tblTransaction
			where OrderID = T1.OrderID and TransactionTypeCode = 'B' and TransactionStatusCode = 'F' ) < @RetryCardCount
	


	fetch next from cOwner into @OwnerID, @RetryCardDelay, @RetryCardCount
end
close cOwner
deallocate cOwner
RETURN 0
